import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


    public class ExcelReader {
        private String filePath;
        private String sheetName;
        private Workbook workBook;
        private Sheet sheet;
        private List<String> columnHeaderList;
        private List<List<String>> listData;
        private List<Map<String, String>> mapData;
        private boolean flag;

        public ExcelReader(String filePath, String sheetName) {
            this.filePath = filePath;
            this.sheetName = sheetName;
            this.flag = false;
            this.load();
        }

        private void load() {
            FileInputStream inStream = null;
            try {
                inStream = new FileInputStream(new File(filePath));
                workBook = WorkbookFactory.create(inStream);
                sheet = workBook.getSheet(sheetName);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (inStream != null) {
                        inStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        private String getCellValue(Cell cell) {
            String cellValue = "";
            DataFormatter formatter = new DataFormatter();
            if (cell != null) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            cellValue = formatter.formatCellValue(cell);
                        } else {
                            double value = cell.getNumericCellValue();
                            int intValue = (int) value;
                            cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellValue = String.valueOf(cell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "";
                        break;
                    default:
                        cellValue = cell.toString().trim();
                        break;
                }
            }
            return cellValue.trim();
        }

        private void getSheetData() {
            listData = new ArrayList<List<String>>();
            mapData = new ArrayList<Map<String, String>>();
            columnHeaderList = new ArrayList<String>();
            int numOfRows = sheet.getLastRowNum() + 1;
            for (int i = 0; i < numOfRows; i++) {
                Row row = sheet.getRow(i);
                Map<String, String> map = new HashMap<String, String>();
                List<String> list = new ArrayList<String>();
                if (row != null) {
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        if (i == 0) {
                            columnHeaderList.add(getCellValue(cell));
                        } else {
                            map.put(columnHeaderList.get(j), this.getCellValue(cell));
                        }
                        list.add(this.getCellValue(cell));
                    }
                }
                if (i > 0) {
                    mapData.add(map);
                }
                listData.add(list);

            }
            flag = true;
        }

        public String getCellData(int row, int col) {
            if (row <= 0 || col <= 0) {
                return null;
            }
            if (!flag) {
                this.getSheetData();
            }
            if (listData.size() >= row && listData.get(row - 1).size() >= col) {
                return listData.get(row - 1).get(col - 1);
            } else {
                return null;
            }
        }

        public String getCellData(int row, String headerName) {
            if (row <= 0) {
                return null;
            }
            if (!flag) {
                this.getSheetData();
            }
            if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {
                return mapData.get(row - 1).get(headerName);
            } else {
                return null;
            }
        }


        public static Workbook readWorkBook(String path) {
            Workbook wb = null;

            try {
                wb = WorkbookFactory.create(new File(path));
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }

            return wb;
        }

        public static void createExcel(Workbook workbook, String path) {
            FileOutputStream fileOut = null;
            try {
                fileOut = new FileOutputStream(path);
                workbook.write(fileOut);
            } catch (Exception e) {
            } finally {
                try {
                    if(fileOut != null) {
                        fileOut.close();
                    }

                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


        public static void main(String[] args) {
            ExcelReader eh = new ExcelReader("/Users/zhaohongbo/Documents/my_info/baohuo/采购单-4.6.xlsx", "Sheet5");
            System.out.println(eh.getCellData(1, 1));
            // System.out.println(eh.getCellData(1, "test1"));
            System.out.println(eh.getCellData(1, 1));
            eh.getSheetData();

        }

}
